package DBLayer;
import ModelLayer.OrderState;
import java.sql.*;
import java.util.ArrayList;

public class DBOrderState {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBOrderState() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public OrderState getOrderStateByID(int id) {
        return selectSingle("SELECT `id_order_state`, `invoice`, `send_email`, `color`, `unremovable`, `hidden`, `logable`, `delivery` FROM `ps_order_state` WHERE `id_order_state` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<OrderState> getOrderStateList() {
        return selectMultiple("SELECT `id_order_state`, `invoice`, `send_email`, `color`, `unremovable`, `hidden`, `logable`, `delivery` FROM `ps_order_state` ORDER BY `id_order_state` ASC;");
    }

    private OrderState selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        OrderState orderState = new OrderState();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                orderState = buildOrderState(result);
            }
            System.out.println("Fetching OrderState Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return orderState;
    }

    private ArrayList<OrderState> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<OrderState> orderStates = new ArrayList<OrderState>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                OrderState orderState = new OrderState();
                orderState = buildOrderState(result);
                orderStates.add(orderState);
            }
            System.out.println("Fetching OrderState List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return orderStates;
    }

    private OrderState buildOrderState(ResultSet result) {
        OrderState orderState = new OrderState();
        try {
            orderState.setIDOrderState(result.getInt("id_order_state"));
            if (result.getInt("invoice") == 1) {
                orderState.setInvoice(true);
            } else {
                orderState.setInvoice(false);
            }
            if (result.getInt("send_email") == 1) {
                orderState.setSendEmail(true);
            } else {
                orderState.setSendEmail(false);
            }
            orderState.setColor(result.getString("color"));
            if (result.getInt("unremovable") == 1) {
                orderState.setUnremovable(true);
            } else {
                orderState.setUnremovable(false);
            }
            if (result.getInt("hidden") == 1) {
                orderState.setHidden(true);
            } else {
                orderState.setHidden(false);
            }
            if (result.getInt("logable") == 1) {
                orderState.setLogable(true);
            } else {
                orderState.setLogable(false);
            }
            if (result.getInt("delivery") == 1) {
                orderState.setDelivery(true);
            } else {
                orderState.setDelivery(false);
            }

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return orderState;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addOrderState(OrderState orderState) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_order_state` (`id_order_state`, `invoice`, `send_email`, `color`, `unremovable`, `hidden`, `logable`, `delivery`) "
                + "VALUES (" + orderState.getIDOrderState() + ", ";
        if (orderState.getInvoice() == true)        { query += "1, "; }
        else                                        { query += "0, "; }
        if (orderState.getSendEmail() == true)      { query += "1, "; }
        else                                        { query += "0, "; }
        query += "'" + orderState.getColor() + "', ";
        if (orderState.getUnremovable() == true)    { query += "1, "; }
        else                                        { query += "0, "; }
        if (orderState.getHidden() == true)         { query += "1, "; }
        else                                        { query += "0, "; }
        if (orderState.getLogable() == true)        { query += "1, "; }
        else                                        { query += "0, "; }
        if (orderState.getDelivery() == true)       { query += "1);"; }
        else                                        { query += "0);"; }

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added OrderState ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateOrderState(OrderState orderState, int id) {
        int returnID = 0;
        Statement stmt = null;
        String query = "UPDATE `ps_order_state` SET "
                + "`id_order_state` = "  + orderState.getIDOrderState() + ", ";
        if (orderState.getInvoice() == true)        { query += "`invoice` = 1, "; }
        else                                        { query += "`invoice` = 0, "; }
        if (orderState.getSendEmail() == true)      { query += "`send_email` = 1, "; }
        else                                        { query += "`send_email` = 0, "; }
        query +=  "`color`          = '" + orderState.getColor() + "', ";
        if (orderState.getUnremovable() == true)    { query += "`unremovable` = 1, "; }
        else                                        { query += "`unremovable` = 0, "; }
        if (orderState.getHidden() == true)         { query += "`hidden` = 1, "; }
        else                                        { query += "`hidden` = 0, "; }
        if (orderState.getLogable() == true)        { query += "`logable` = 1, "; }
        else                                        { query += "`logable` = 0, "; }
        if (orderState.getDelivery() == true)       { query += "`delivery` = 1 "; }
        else                                        { query += "`delivery` = 0 "; }
        query += "WHERE `id_order_state` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = orderState.getIDOrderState();
            System.out.println("Updated OrderState ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteOrderState(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_order_state` WHERE `id_order_state` = " + id + " LIMIT 1;");
            System.out.println("Deleted OrderState ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
